/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import java.sql.ResultSet;
import java.util.ArrayList;
import POJO.NhanVien;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Khoi
 */
public class NhanVienDao {

	public static ArrayList<NhanVien> layDanhSach() {
		ArrayList<NhanVien> ds = new ArrayList<NhanVien>();
		String str = "SELECT * FROM NHANVIEN";
		String sql = str;

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				NhanVien tmp = new NhanVien();
				tmp.setMaNV(rs.getInt("MaNV"));
				tmp.setTenNV(rs.getString("TenNV"));
				tmp.setUserName(rs.getString("Username"));
				tmp.setPassWord(rs.getString("Password"));
				tmp.setLoaiNV(rs.getInt("LoaiNV"));
				tmp.setTrangThai(rs.getInt("TrangThai"));

				ds.add(tmp);
			}
//		} catch (Exception ex) {
//			System.out.println(ex.getMessage());
		} catch (Exception ex) {
			Logger.getLogger(NhanVienDao.class.getName()).log(Level.SEVERE, null, ex);
			//	System.out.println(ex.getMessage());

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static int layMaxMaNV() {
		String sql = "select nv.MaNV from NhanVien nv where nv.MaNV >= all(select nv1.MaNV from NhanVien nv1 ); ";
		KetNoi cn = null;
		ResultSet rs = null;
		int n = 0;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				n = rs.getInt("MaNV");
			}
		} catch (Exception ex) {
			Logger.getLogger(NhanVienDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return n;
	}

	public static String layTenNV(int maNV) {
		String sql = "select TenNV from NhanVien where MaNV = " + maNV;
		KetNoi cn = null;
		ResultSet rs = null;
		String n = "";

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				n = rs.getString("TenNV");
			}
		} catch (Exception ex) {
			Logger.getLogger(NhanVienDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return n;
	}

	public static boolean them(NhanVien tmp) {
		boolean kq = false;
		int mkq = layMaxMaNV() + 1;
		tmp.setMaNV(mkq);
		String sql = "INSERT INTO  NhanVien  (MaNV,TenNV,Username,Password,LoaiNV,TrangThai) VALUES ";

		String s = String.format("( %d, N'%s',N'%s',N'%s',%d,%d);", tmp.getMaNV(), tmp.getTenNV(), tmp.getUserName(), tmp.getPassWord(), tmp.getLoaiNV(), tmp.getTrangThai());
		sql += s;
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(NhanVienDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		if (n == 1) {
			kq = true;
		}

		return kq;
	}

	public static boolean kiemTraTruocKhiXoa(int MaNV) {
		String sql = "select nv.MaNV "
			 + "from NhanVien nv, HoaDon hd, PXuatKho px, PNhapKho pn "
			 + "where (nv.MaNV = hd.MaNVPhuTrach  and  MaNV ="
			 + " ) "
			 + "or(  nv.MaNV = px.MaNVPhuTrach and  MaNV ="
			 + " ) "
			 + "or( nv.MaNV = pn.MaNVPhuTrach and  MaNV ="
			 + " ) ";
		boolean kq = false;

		KetNoi cn = null;

		try {
			cn = new KetNoi();
			kq = cn.kiemTra(sql);
		} catch (SQLException ex) {
			Logger.getLogger(NhanVienDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		return kq;

	}

	public static boolean xoa(int MaNV) {
		boolean kq = false;
		String str = String.format("if not exists(select pn.MaNVPhuTrach from  PNhapKho pn where pn.MaNVPhuTrach = %d ) "
			 + " and not exists(select px.MaNVPhuTrach from  PXuatKho px where px.MaNVPhuTrach = %d )"
			 + " and not exists(select hd.MaNVPhuTrach from  HoaDon hd where hd.MaNVPhuTrach = %d ) ", MaNV, MaNV, MaNV);
		String sql = String.format(str + "DELETE FROM NhanVien WHERE MaNV=%d", MaNV);
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(NhanVienDao.class.getName()).log(Level.SEVERE, null, ex);
		}
		if (n >= 1) {
			kq = true;
		}
		cn.dongKetNoi();
		return kq;
	}

	public static boolean capNhat(NhanVien tmp) {
		boolean kq = false;
		String sql = String.format("UPDATE NhanVien "
			 + "SET "
			 + "TenNV = N'%s', "
			 + "Username =N'%s', "
			 + "Password =N'%s', "
			 + "LoaiNV = %d, "
			 + "TrangThai =%d "
			 + "WHERE  MaNV = %d ; ",
			 tmp.getTenNV(),
			 tmp.getUserName(),
			 tmp.getPassWord(),
			 tmp.getLoaiNV(),
			 tmp.getTrangThai(),
			 tmp.getMaNV());
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(NhanVienDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		if (n == 1) {
			kq = true;
		}

		return kq;
	}

	public static NhanVien layByMa(int MaNV) {
		NhanVien tmp = null;
		try {
			String sql = String.format("SELECT * FROM NhanVien WHERE MaNV =" + MaNV);
			KetNoi cn = new KetNoi();
			ResultSet rs = cn.docBang(sql);
			if (rs.next()) {
				int maNV = rs.getInt("MaNV");
				String tenNV = rs.getString("TenNV");
				String username = rs.getString("Username");
				String password = rs.getString("Password");
				int loaiNV = rs.getInt("LoaiNV");
				int trangThai = rs.getInt("TrangThai");
				tmp = new NhanVien(maNV, tenNV, username, password, loaiNV, trangThai);
			}
			cn.dongKetNoi();
		} catch (Exception ex) {
			Logger.getLogger(NhanVienDao.class.getName()).log(Level.SEVERE, null, ex);
		}
		return tmp;
	}

	public static NhanVien getByUsername(String userName) {
		NhanVien tmp = null;
		String sql = String.format("SELECT * FROM NhanVien WHERE Username ='%s';", userName);
		KetNoi cn = null;
		try {
			cn = new KetNoi();
			ResultSet rs = cn.docBang(sql);
			if (rs.next()) {
				int maNV = rs.getInt("MaNV");
				String tenNV = rs.getString("TenNV");
				String username = rs.getString("Username");
				String password = rs.getString("Password");
				int loaiNV = rs.getInt("LoaiNV");
				int trangThai = rs.getInt("TrangThai");
				tmp = new NhanVien(maNV, tenNV, username, password, loaiNV, trangThai);
			}
			cn.dongKetNoi();
		} catch (Exception ex) {
			Logger.getLogger(NhanVienDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		return tmp;
	}
}
